﻿USE Tamias
GO

DECLARE @contentSchema INT

IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = 'Content')
BEGIN
	EXEC('CREATE SCHEMA Content AUTHORIZATION dbo');
	SET @contentSchema = SCOPE_IDENTITY();
END
ELSE
BEGIN
	SET @contentSchema = (SELECT SCHEMA_ID FROM sys.schemas WHERE name = 'Content');
END

/* ================================================================================ */
/*									THE DROP ZONE									*/
/* ================================================================================ */

IF EXISTS (SELECT * FROM sys.foreign_keys WHERE name = 'FK_Content_ContentItem_ContentGroupId' AND schema_id = @contentSchema)
BEGIN
	ALTER TABLE Content.ContentItem DROP FK_Content_ContentItem_ContentGroupId
	PRINT 'Dropped key FK_Content_ContentItem_ContentGroupId'
END

PRINT 'Key Dropping Finished'



IF EXISTS (SELECT * from sys.tables WHERE name = 'ContentItem' AND type='U' AND schema_id = @contentSchema)
BEGIN
	DROP TABLE Content.ContentItem
	PRINT 'Dropped table Content.ContentItem'
END

IF EXISTS (SELECT * FROM sys.tables WHERE name = 'ContentGroup' AND type='U' AND schema_id = @contentSchema)
BEGIN
	DROP TABLE Content.ContentGroup
	PRINT 'Dropped table Content.ContentGroup'
END

PRINT 'Table Dropping Finished'


/* ================================================================================ */
/*										TABLES										*/
/* ================================================================================ */


CREATE TABLE Content.[ContentGroup]
(
	ContentGroupId INT IDENTITY,
	ContentGroupName NVARCHAR(50) NOT NULL,
	CONSTRAINT [PK_Content_ContentGroup_ContentGroupId] PRIMARY KEY CLUSTERED (ContentGroupId),
	CONSTRAINT [UQ_ContentGroupName_ApplicationId] UNIQUE (ContentGroupName)
)

CREATE TABLE Content.[ContentItem]
(
	ContentItemId INT IDENTITY,
	ContentItemName NVARCHAR(100) NOT NULL,
	ContentItemText NVARCHAR(MAX) NOT NULL,
	ContentItemDescription NVARCHAR(MAX) NULL,
	ContentGroupId INT NOT NULL,
	CONSTRAINT [PK_Content_ContentItem_ContentItemId] PRIMARY KEY CLUSTERED (ContentItemId),
	CONSTRAINT [FK_Content_ContentItem_ContentGroupId] FOREIGN KEY (ContentGroupId) REFERENCES Content.[ContentGroup] (ContentGroupId),
	CONSTRAINT [UQ_Content_ContentItem_ContentItemName_ContentGroupId] UNIQUE (ContentItemName, ContentGroupId) 
)

PRINT 'Table Creation Finished'

/* ================================================================================ */
/*									Sample Data										*/
/* ================================================================================ */
	
INSERT INTO Content.ContentGroup (ContentGroupName) SELECT 'Home'
INSERT INTO Content.ContentItem (ContentItemName, ContentItemText, ContentItemDescription, ContentGroupId)
	SELECT 'HomePageDynamicContent', '<p>This is content out of the database.</p>', 'Sample content item.', SCOPE_IDENTITY();
INSERT INTO Content.ContentGroup (ContentGroupName) SELECT 'CustomUrl'

PRINT 'Sample Data Insert Finished'
